var pg = require('pg');
var _ = require('underscore');
var config = require('../lib/config');
var logger = require('./logger');
// var Promise = require('bluebird'); 
var Q = require('q')

exports.DB = DB = function(connstr){
	this.conString = connstr || config.db.default;
	this.client = null;
	this.done = null;
	this.transaction = false;
	this.begin = function(cb){
		var that = this;
		pg.connect(this.conString,function(err,client,done){
			if(err) return cb(err);
			that.client = client;
			that.done = done;
			that.transaction = true;			
			client.query("BEGIN",function(err,result){
				logger.info("开始执行事务！！！");
				if(err){
					logger.error(err);
					return cb(err);
				} 
				return cb(null,result.rows);
			})
		})
	};
	this.query = function(sql,params,cb){
		if(!_.isArray(params)){
			params = [];
		}
		if(config.db.print_sql){
			logger.info("[sqltext] " + sql);
			logger.info("[params] " + params);
		}
		if(this.client){
			var that = this;
			this.client.query(sql,params,function(err,result){
				if(err){
					logger.error(err);
					return cb(err);
				}
				return cb(null,result.rows);
			})
		}else{
			pg.connect(this.conString,function(err,client,done){
				 // var pool = pg.pools.getOrCreate();

  			// poolSize: 1, availableObjects: 0
  			// console.log('poolSize: %d, availableObjects: %d', pool.getPoolSize(), pool.availableObjectsCount()); 

				if(err) {
					logger.error(err);
					return cb(err);
				}
				client.query(sql,params,function(err,result){
					done();
					if(err){
						logger.error(err);
						return cb(err);
					}
					return cb(null,result.rows);
				})
			})
		}		
	};
	this.commit = function(cb){
		var that = this;
		this.client.query('COMMIT',function(err){
			that.done();
			if(err){
				logger.error(err);
				return cb(err);
			} 
			return cb(null);
		})
	};
	this.rollback = function(cb){
		console.log(cb);
		var that = this;
		that.client.query('ROLLBACK',function(err){
			that.done();
			if(err) {
				logger.error(err);
				return cb(err);
			}
			return cb(null);
		})
	};
	this.queryList = function(sql,params,cb){
		this.query(sql,params,cb);
	};
	this.queryInt = function(sql,params,cb){
		this.query(sql,params,function(err,rows){
			if(err) return cb(err);
			for(i in rows[0]){
				return cb(null,parseInt(rows[0][i]));
			}
			return cb(null,0);
		})
	};
	this.queryString = function(sql,params,cb){
		this.query(sql,params,function(err,rows){
			if(err) return cb(err);
			for(i in rows[0]){
				return cb(null,rows[0][i]);
			}
			return cb(null,"");
		})
	};
	this.queryPage = function(sql,params,page,limit,cb){
		var that = this; //可能遇到作用域问题，如果不用that
		var _sql = "select count(1) as counter from (" + sql + ") _sub";
		var result = {};
		page = page || 1;
		limit = limit || 20;
		that.query(_sql,params,function(err,rows){
			if(err){
				return cb(err);
			}
			result.count = parseInt(rows[0].counter);
			var offset = (parseInt(page) -1 ) * limit;
			_sql = sql + " limit " + limit + " offset " + offset;
			that.query(_sql,params,function(err,rows){
				if (err) return cb(err);
				result.data = rows;
				result.pageSize = parseInt(limit);
				result.pageNum = parseInt(page);
				result.pageCount = Math.ceil(result.count/result.pageSize);
				return cb(null,result);
			})
		})
	};
	this.insert = function(table_name,fields,returning,cb){
		var i = 1;
		var params = [];
		var keys = [];
		var param_indexs = [];
		for(key in fields){
			param_indexs.push("$" + i++);
			keys.push(key);
			params.push(fields[key]);
		}
		var sql = "insert into " + table_name + "(" + keys.toString() + ") ";
		sql += " values(" + param_indexs.toString() + ") ";
		if(typeof(returning) == 'function'){
			cb = returning;
		}
		else if(returning){
			sql += " returning " + returning;
		}
		return this.query(sql,params,function(err,rows){
			if(err) return cb(err);
			return cb(null,rows[0]);
		})
	};
	this.update = function(table_name,fields,conditions,cb){
		var params = [];
		var i = 1;
		var par = [];
		var tmp_fields = [];
		for(key in fields){
			var _tmpstr = key + " = $" + i++;
			params.push(fields[key]);  
			tmp_fields.push(_tmpstr);
		}
		var tmp_conditions = [];
		for(key in conditions){
			var _tmpstr = key + " = $" + i++;
			params.push(conditions[key]);
			tmp_conditions.push(_tmpstr);
		}
		var sql = "update " + table_name + " set " + tmp_fields.toString();
		sql += " where " + tmp_conditions.join(" and ")
		return this.query(sql,params,function(err,rows){
			if(err) return cb(err);
			return cb(null,rows);
		})
	}
	this.queryMap = function(sql,params,cb){
		this.query(sql,params,function(err,rows){
			if(err) return cb(err);
			if(rows.length > 0){
				return cb(null,rows[0]);
			}else{
				return cb(null,null);
			}
			
		})
	};
}


exports.DB_PROMISE = function(connstr){
	this.conString = connstr || config.db.default;
	this.client = null;
	this.done = null;
	this.begin = function(client){
		var that = this;
		var deferred = Q.defer();
		pg.connect(that.conString,function(err,client,done){
			if(err){
				logger.error(err);
				deferred.reject(err);
			}else{
				that.client = client;
				that.done = done;
				that.client.query("BEGIN",function(err,result){
					if(err){
						logger.error(err);
						deferred.reject(err);
					}else{
						logger.info("begin transaction...");
						deferred.resolve(result.rows);
					}
					console.log(deferred);
				})
			}
		})
		return deferred.promise;		
	};
	this.query = function(sql,params){
		var that = this;
		var deferred = Q.defer();
		that.client.query(sql,params,function(err,result){
			if(err){
				logger.error(err);
				deferred.reject(err);
			}else{
				deferred.resolve(result.rows);
			}
		})
		return deferred.promise;
	};
	this.rollback = function(cb){
		var that = this;
		var deferred = Q.defer();
		this.client.query('ROLLBACK', function(err,result) {
    	that.done();
    	if(err){
    		logger.error(err);
    		deferred.reject(err);
    	}
    	logger.info("transaction rollback...");
    	deferred.resolve(result.rows);    	
  	});
  	return deferred.promise;
	};
	this.commit = function(cb){
		var that = this;
		var deferred = Q.defer();
		this.client.query('COMMIT',function(err,result){
			that.done();
			if(err){
				logger.error(err);
				deferred.reject(err);
			}
			deferred.resolve(result.rows);
		})
		return deferred.promise;
	}
}